# encoding: utf-8
# @author: wangxin
# @software: garner
# @file: getBusinessReportDataViews.py
# @time: 2025/1/22 10:26
from turtledemo.penrose import start

import pymysql
from django.db.models.expressions import result
from django.views import View
import json
from tabnanny import check

# 导入Django核心模块中的序列化器，用于将Django模型转换为其他格式，如JSON、XML等
from django.core import serializers

# 导入Django序列化功能，以便在需要时将模型序列化
from django.core.serializers import serialize
from django.db.models import Q

# 导入HttpResponse和JsonResponse类，用于构建HTTP响应和JSON响应
from django.http import HttpResponse, JsonResponse

# 导入render函数，它用于根据请求和模板渲染网页
from django.shortcuts import render
import time

from django.views import View
class getBusinessReportDataView(View):

    def get(self,request):
        datatime=time.strftime("%Y-%m-%d", time.localtime())
        conn = pymysql.Connect(
            host="127.0.0.1",
            port=3306, user="root",
            password="123456",
            db="ruici",
            charset="utf8",
            autocommit=True
        )
        cursor = conn.cursor()
        sql = """
            SELECT 
            s.NAME,
            o_grouped.order_count,
            (SELECT COUNT(*) FROM t_order) AS total_orders
        FROM 
            (SELECT 
                 o.setmeal_id, 
                 COUNT(o.id) AS order_count
             FROM 
                 t_order AS o 
             GROUP BY 
                 o.setmeal_id
            ) AS o_grouped
        INNER JOIN 
            t_setmeal AS s 
        ON 
            o_grouped.setmeal_id = s.id;
                        
            """

        cursor.execute(sql)
        res = cursor.fetchall()
        reportData_dict = dict()
        data_list = list()
        for data in res:
            data_dict = dict()
            data_dict['name'] = data[0]
            data_dict['setmeal_count'] = data[1]
            data_dict['proportion']= round(data[1]/data[2],ndigits=3)
            data_list.append(data_dict)


            reportData_dict['reportDate'] = datatime
            reportData_dict['hotSetmeal'] = data_list
            sql2=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_member 
            WHERE 
                regTime = '{datatime}';
            """
            cursor.execute(sql2)
            todayNewMember = cursor.fetchone()
            reportData_dict['todayNewMember'] = todayNewMember
            sql3 = f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_member 
           
            """
            cursor.execute(sql3)
            totalMember = cursor.fetchone()
            reportData_dict['totalMember'] = totalMember
            endTime= time.strftime("%Y-%m-%d", time.localtime())
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24))
            sql4=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_member 
            WHERE 
                regTime >= '{startTime}' AND regTime <= '{endTime}'
            """
            cursor.execute(sql4)
            thisWeekNewMember = cursor.fetchone()
            reportData_dict['thisWeekNewMember'] = thisWeekNewMember
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24  * 7))
            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql5 = f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_member 
            WHERE 
                regTime >= '{startTime}' AND regTime <= '{endTime}'
            """
            cursor.execute(sql5)
            thisWeekNewMember= cursor.fetchone()
            reportData_dict['thisWeekNewMember'] = thisWeekNewMember
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24 * 30))

            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql6=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_member 
            WHERE 
                regTime >= '{startTime}' AND regTime <= '{endTime}'
            """
            cursor.execute(sql6)
            thisMonthNewMember = cursor.fetchone()
            reportData_dict['thisMonthNewMember'] = thisMonthNewMember
            #预约人数统计


            sql7=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_order 
            WHERE 
                orderDate = '{datatime}'
            """
            cursor.execute(sql7)
            todayOrderNumber = cursor.fetchone()
            reportData_dict['todayOrderNumber'] = todayOrderNumber

            sql8=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_order 
            WHERE 
                 orderDate = '{datatime}'
            ANd 
                orderStatus ='已到诊'    
            """
            cursor.execute(sql8)
            todayVisitsNumber = cursor.fetchone()
            reportData_dict['todayVisitsNumber'] = todayVisitsNumber
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24 * 7))
            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql9=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_order 
            WHERE 
                 orderDate >= '{startTime}' AND orderDate <= '{endTime}'
            
            """
            cursor.execute(sql9)
            thisWeekOrderNumber = cursor.fetchone()
            reportData_dict['thisWeekOrderNumber'] = thisWeekOrderNumber

            sql10=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_order 
            WHERE 
                 orderDate >= '{startTime}' AND orderDate <= '{endTime}'
            ANd 
                orderStatus ='已到诊'    
            """
            cursor.execute(sql10)
            thisWeekVisitsNumber = cursor.fetchone()
            reportData_dict['thisWeekVisitsNumber'] = thisWeekVisitsNumber
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24 * 30))
            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql11=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_order 
            WHERE 
                 orderDate >= '{startTime}' AND orderDate <= '{endTime}'
            
            """
            cursor.execute(sql11)
            thisMonthOrderNumber = cursor.fetchone()
            reportData_dict['thisMonthOrderNumber'] = thisMonthOrderNumber
            sql12=f"""
            SELECT 
                COUNT(id) 
            FROM 
                t_order 
            WHERE 
                 orderDate >= '{startTime}' AND orderDate <= '{endTime}'
            ANd 
                orderStatus ='已到诊'    
            """
            cursor.execute(sql12)
            thisMonthVisitsNumber = cursor.fetchone()
            reportData_dict['thisMonthVisitsNumber'] = thisMonthVisitsNumber







        result = {
            "code": 200,
            "data":reportData_dict
        }
        return JsonResponse(result)